# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
import pymongo

class DbdyTestPipeline:
    def process_item(self, item, spider):
        print(item)
        return item


class MysqlPipeline:

    def open_spider(self, spider):
        """
        启动项目时执行 可以在此进行初始化配置
        :param spider:
        :return:
        """
        if spider.name == 'dbtop250':
            self.db = pymysql.connect(host="localhost", port=3306, user="root", passwd="888888",db="test")
            self.cursor = self.db.cursor()

        sql = """
            create table if not exists db_movie2 (
                id int auto_increment primary key,
                quote varchar(255),
                rating varchar(255) not null,
                title varchar(255) not null,
                description text
            );
        """
        try:
            self.cursor.execute(sql)
            print('表创建成功')
        except Exception as e:
            print(e,"表创建失败")

    def process_item(self, item, spider):
        sql = """
            insert into db_movie2(id,quote,rating,title,description) values (%s,%s,%s,%s,%s);
        """

        try:
            self.cursor.execute(sql, (0,item['quote'],item['rating'],item['title'],item['info_data']))
            self.db.commit()
            print('数据插入成功')
        except Exception as e:
            self.db.rollback()
            print('数据插入失败',e)
        return item

    def close_spider(self, spider):
        if spider.name == 'dbtop250':
            self.db.close()


class MongoDBPipeline:

    def open_spider(self, spider):
        if spider.name == 'dbtop250':
            self.client = pymongo.MongoClient('localhost', 27017)
            self.collection = self.client['db_movie']['info2']


    def process_item(self, item, spider):
        self.collection.insert_one(dict(item))
        return item

    def close_spider(self, spider):
        if spider.name == 'dbtop250':
            self.client.close()

